package com.jackrain.nea.wms.mapper;

import com.alibaba.fastjson.JSONObject;
import com.jackrain.nea.wms.utils.ObjectToUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.apache.ibatis.jdbc.SQL;

import java.util.Date;
import java.util.Map;
import java.util.Set;

@Slf4j
public class WmsSqlProvider {

    public final static FastDateFormat DATE_FORMAT = FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss");

    public String updateSql(Map<String, Object> para) {

        String tableName = ObjectToUtil.getString(para.get("tableName"), "");


        String sql = new SQL() {
            {
                UPDATE(tableName);

                JSONObject setKeys = (JSONObject) para.get("setKeys");
                for (String key : setKeys.keySet()) {
                    if ("id".equalsIgnoreCase(key)) {
                        continue;
                    }
//                    if (setKeys.getString(key) == null) {
//                        continue;
//                    }
                    SET(key + "= #{setKeys." + key + "}");
                }

                JSONObject whereKeys = null;
                if (para.containsKey("whereKeys")) {
                    whereKeys = (JSONObject) para.get("whereKeys");
                }
                String whereStr = null;
                if (para.containsKey("whereStr")) {
                    whereStr = ObjectToUtil.getString(para.get("whereStr"), "");
                }
                if (whereKeys != null) {
                    for (String key : whereKeys.keySet()) {
                        if (whereKeys.getString(key) == null) {
                            continue;
                        }
                        WHERE(key + "= #{whereKeys." + key + "}");
                    }
                }
                if (!ObjectToUtil.isNullOREmpty(whereStr)) {
                    WHERE(whereStr);
                }

            }
        }.toString();

        System.out.print("sql ========>" + sql + "\r\n");

        if (!sql.toLowerCase().contains("where")) {
            System.out.print("update must have where " + "\r\n");
            return "";
        }

        return sql;
    }

    public String insertSql(Map<String, Object> para) {

        String tableName = ObjectToUtil.getString(para.get("tableName"), "");
        JSONObject insertKeys = (JSONObject) para.get("insertKeys");

        String sql = new SQL() {
            {
                Set<String> keySet = insertKeys.keySet();
                INSERT_INTO(tableName);
                for (int i = 0; i < insertKeys.size(); i++) {
                    String key = (String) keySet.toArray()[i];
                    if (insertKeys.getString(key) == null) {
                        continue;
                    }
                    VALUES(key, "#{insertKeys." + key + "}");
                }
            }
        }.toString();

        System.out.print("sql ========>" + sql + "\r\n");

        return sql;
    }


    public String selectSql(Map<String, Object> para) {

        System.out.println(para.toString());

        String tableName = ObjectToUtil.getString(para.get("tableName"), "");
        String[] fields = (String[]) para.get("fields");

        String  fieldStr= StringUtils.join(fields, ",");


        String sql = new SQL() {
            {
                JSONObject whereKeys = null;
                if (para.containsKey("whereKeys")) {
                    whereKeys = (JSONObject) para.get("whereKeys");
                }
                String whereStr = null;
                if (para.containsKey("whereStr")) {
                    whereStr = ObjectToUtil.getString(para.get("whereStr"), "");
                }
                String order = null;
                if (para.containsKey("order")) {
                    order = ObjectToUtil.getString(para.get("order"), "");
                }

                SELECT(fields);

                if (!ObjectToUtil.isNullOREmpty(order)) {
                    FROM(
                            "( select * from " + tableName + order + ")"
                    );
                } else {
                    FROM(
                            tableName
                    );
                }
                if (whereKeys != null) {
                    for (String key : whereKeys.keySet()) {
                        if (whereKeys.getString(key) == null) {
                            continue;
                        }
                        WHERE(key + "= #{whereKeys." + key + "}");
                    }
                }
                if (!ObjectToUtil.isNullOREmpty(whereStr)) {
                    WHERE(whereStr);
                }
//                if (!ObjectToUtil.isNullOREmpty(order)) {
//                    ORDER_BY(order);
//                }
            }
        }.toString();

        String limit = null;
        if (para.containsKey("limit")) {
            limit = ObjectToUtil.getString(para.get("limit"), "");
        }

        if (!ObjectToUtil.isNullOREmpty(limit)) {
            sql += " where rownum <= " + limit;
        }

        System.out.print("selectsql ========>" + sql + "\r\n");
        log.debug("selectsql ========>" + sql);

        return sql;
    }

    public String deleteSql(Map<String, Object> para) {

        System.out.println(para.toString());

        String tableName = ObjectToUtil.getString(para.get("tableName"), "");
        JSONObject whereKeys = (JSONObject) para.get("whereKeys");

        String sql = new SQL() {
            {
                DELETE_FROM(tableName);
                for (String key : whereKeys.keySet()) {
                    if (whereKeys.getString(key) == null) {
                        continue;
                    }
                    WHERE(key + "= #{whereKeys." + key + "}");
                }
            }
        }.toString();

        System.out.print("sql ========>" + sql + "\r\n");

        if (!sql.toLowerCase().contains("where")) {
            System.out.print("delete must have where " + "\r\n");
            return "";
        }

        return sql;
    }

    public String insertSqlOracle(Map<String, Object> para) {

        String tableName = ObjectToUtil.getString(para.get("tableName"), "");
        JSONObject insertKeys = (JSONObject) para.get("insertKeys");

        String sql = new SQL() {
            {
                Set<String> keySet = insertKeys.keySet();
                INSERT_INTO(tableName);
                for (int i = 0; i < insertKeys.size(); i++) {
                    String key = (String) keySet.toArray()[i];
                    if (insertKeys.getString(key) == null) {
                        continue;
                    }
                    if (insertKeys.get(key) instanceof String) {
                        VALUES(key, "'" + insertKeys.getString(key) + "'");
                    } else if (insertKeys.get(key) instanceof Date) {
                        VALUES(key, "to_date('" + DATE_FORMAT.format(insertKeys.getDate(key)) + "','yyyy-mm-dd hh24:mi:ss')");
                    } else {
                        VALUES(key, String.valueOf(insertKeys.get(key)));
                    }

                }
            }
        }.toString();

        System.out.print("sql ========>" + sql + "\r\n");

        return sql;
    }

    public String updateSqlOracle(Map<String, Object> para) {

//        SELECT
//#{item.emplId},to_date(#{item.effdt,jdbcType=DATE},'YYYY-MM-DD'),#{item.relationship},#{item.name},#{item.sex},
//        to_date(#{item.birthDate,jdbcType=DATE},'YYYY-MM-DD'),
//#{item.political},#{item.nationalId},#{item.relatedJob},#{item.phone},#{item.memo,javaType=string,jdbcType=CLOB}
//        FROM DUA

        String tableName = ObjectToUtil.getString(para.get("tableName"), "");
        JSONObject setKeys = (JSONObject) para.get("setKeys");
        JSONObject whereKeys = (JSONObject) para.get("whereKeys");

        String sql = new SQL() {
            {
                UPDATE(tableName);
                for (String key : setKeys.keySet()) {
                    if (!key.equals("ID")) {
                        if (setKeys.getString(key) == null) {
                            continue;
                        }
                        if (setKeys.get(key) instanceof String) {
                            SET(key + "= '" + setKeys.getString(key) + "'");
                        } else if (setKeys.get(key) instanceof Date) {
                            SET(key + "= " + "to_date('" + DATE_FORMAT.format(setKeys.getDate(key)) + "','yyyy-mm-dd hh24:mi:ss')");
                        } else {
                            SET(key + "= " + setKeys.get(key));
                        }
                    }
                }
                for (String key : whereKeys.keySet()) {
                    if (setKeys.getString(key) == null) {
                        continue;
                    }
                    if (whereKeys.get(key) instanceof String) {
                        WHERE(key + "= '" + whereKeys.get(key) + "'");
                    } else if (setKeys.get(key) instanceof Date) {
                        WHERE(key + "= " + "to_date('" + DATE_FORMAT.format(setKeys.getDate(key)) + "','yyyy-mm-dd hh24:mi:ss')");
                    } else {
                        WHERE(key + "= " + whereKeys.get(key));
                    }
                }

            }
        }.toString();

        System.out.print("sql ========>" + sql + "\r\n");

        return sql;
    }


}


